import pymysql
from flask import Flask, render_template, request, redirect, url_for, session, jsonify
from markdown_it.rules_core.normalize import NULL_RE

PER_PAGE = 50

app = Flask(__name__)
# 设置一个用于加密 session 的密钥
app.secret_key = '88888888'

db_config = { 
    'host' : '127.0.0.1',#localhost
    'user' : 'schools',
    'password' : '88888888',
    'database' : 'schoolss',
    'charset' : 'utf8mb4'
}

@app.route('/')
def index():
    return render_template('index.html')
#首页的四个子页面，等待加入返回键
@app.route('/index/student')
def index_student():
    return render_template('index_student.html')

@app.route('/index/teacher')
def index_teacher():
    return render_template('index_teacher.html')

@app.route('/index/course')
def index_course():
    return render_template('index_course.html')

@app.route('/index/choice')
def index_choice():
    return render_template('index_choice.html')

@app.route('/add/student', methods=['GET', 'POST'])
def add_student():
    if request.method == 'GET':
        return render_template('add_student.html')
    
    sid = request.form.get('sid')
    sname = request.form.get('sname')
    email = request.form.get('email')
    grade = request.form.get('grade')

    conn = pymysql.connect(host = db_config['host'],  user = db_config['user'], password = db_config['password'], database = db_config['database'])
    cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)

    sql = "INSERT INTO students (sid, sname, email, grade) VALUES (%s, %s, %s, %s)"
    cursor.execute(sql, (sid, sname, email, grade))
    conn.commit()

    cursor.close()
    conn.close()
    return '添加学生成功'

@app.route('/show/students')
def show_students():
    try:
        # 获取请求的页码，默认为第 1 页
        page = request.args.get('page', 1, type=int)
        offset = (page - 1) * PER_PAGE

        # 连接数据库
        conn = pymysql.connect(host = db_config['host'],  user = db_config['user'], password = db_config['password'], database = db_config['database'])
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

        # 查询当前页的学生数据
        sql = "SELECT * FROM students LIMIT %s OFFSET %s"
        cursor.execute(sql, (PER_PAGE, offset))
        students = cursor.fetchall()

        # 查询学生总数
        cursor.execute("SELECT COUNT(*) AS total FROM students")
        total_students = cursor.fetchone()['total']

        # 计算总页数
        total_pages = (total_students + PER_PAGE - 1) // PER_PAGE

        # 关闭连接
        cursor.close()
        conn.close()

        # 渲染模板
        return render_template('show_students.html', students=students, page=page, total_pages=total_pages)

    except Exception as e:
        print(f"数据库操作失败: {e}")
        return render_template('show_students.html', error="数据库操作失败，请稍后重试")


@app.route('/search/students', methods=['GET', 'POST'])
def get_student_sid():
    if request.method == 'GET':
        return render_template('input_student.html')

    sid = request.form.get('sid')
    sname = request.form.get('sname')
    email = request.form.get('email')
    grade = request.form.get('grade')

    #全改了
    cnt = 0
    if sid != "":
        cnt += 1
    if sname != "":
        cnt += 1
    if email != "":
        cnt += 1
    if grade != "":
        cnt += 1

    if sid:
        student = query_student_by_sid(sid)
    elif email:
        student = query_student_by_email(email)
    elif sname:
        student = query_student_by_sname(sname)
    elif grade:
        student = query_student_by_grade(grade)
    else:
        return render_template('show_single_stu.html', student=None, error="至少填写一个查询条件")

    if (email != "" and sid != "" and student != None):
        if (student['email'] == email and student['sid'] == sid):
            student = student
        else:
            student = None

    if not student:
        return render_template('show_single_stu.html', student=None, error="未找到该学生")
    elif cnt == 1 and (sname != "" or grade != ""):
        return render_template('show_single_stu.html', student=None, error="无法找到准确的学生")
    elif cnt == 2 and (sname != "" and grade != ""):
        return render_template('show_single_stu.html', student=None, error="无法找到准确的学生")
    return render_template('show_single_stu.html', student=student)

#使用sid来查询学生信息
def query_student_by_sid(sid):
    try:
        conn = pymysql.connect(host = db_config['host'],  user = db_config['user'], password = db_config['password'], database = db_config['database'])
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = "SELECT * FROM students WHERE sid = %s"
        cursor.execute(sql, (sid,))
        return cursor.fetchone()
    except Exception as e:
        print(f"数据库操作失败: {e}")
        return None
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

#使用sname来查询学生信息
def query_student_by_sname(sname):
    try:
        conn = pymysql.connect(host = db_config['host'],  user = db_config['user'], password = db_config['password'], database = db_config['database'])
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = "SELECT * FROM students WHERE sname = %s"
        cursor.execute(sql, (sname,))
        return cursor.fetchone()
    except Exception as e:
        print(f"数据库操作失败: {e}")
        return None
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

#使用email来查询学生信息
def query_student_by_email(email):
    try:
        conn = pymysql.connect(host = db_config['host'],  user = db_config['user'], password = db_config['password'], database = db_config['database'])
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = "SELECT * FROM students WHERE email = %s"
        cursor.execute(sql, (email,))
        return cursor.fetchone()
    except Exception as e:
        print(f"数据库操作失败: {e}")
        return None
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

#使用grade来查询学生信息
def query_student_by_grade(grade):
    try:
        conn = pymysql.connect(host = db_config['host'],  user = db_config['user'], password = db_config['password'], database = db_config['database'])
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = "SELECT * FROM students WHERE grade = %s"
        cursor.execute(sql, (grade,))
        return cursor.fetchone()
    except Exception as e:
        print(f"数据库操作失败: {e}")
        return None
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


#teachers表
#首先是获取teachers表
@app.route('/show/teachers')
def show_teachers():
    try:
        # 获取请求的页码，默认为第 1 页
        page = request.args.get('page', 1, type=int)
        offset = (page - 1) * PER_PAGE

        # 连接数据库
        conn = pymysql.connect(host = db_config['host'],  user = db_config['user'], password = db_config['password'], database = db_config['database'])
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

        # 查询当前页的老师数据
        sql = "SELECT * FROM teachers LIMIT %s OFFSET %s"
        cursor.execute(sql, (PER_PAGE, offset))
        teachers = cursor.fetchall()

        # 查询学生总数
        cursor.execute("SELECT COUNT(*) AS total FROM teachers")
        total_teachers = cursor.fetchone()['total']

        # 计算总页数
        total_pages = (total_teachers + PER_PAGE - 1) // PER_PAGE

        # 关闭连接
        cursor.close()
        conn.close()

        # 渲染模板
        return render_template('show_teachers.html', teachers=teachers, page=page, total_pages=total_pages)

    except Exception as e:
        print(f"数据库操作失败: {e}")
        return render_template('show_teachers.html', error="数据库操作失败，请稍后重试")
    
#在teachers表中添加数据
@app.route('/add/teacher', methods=['GET', 'POST'])
def add_teachers():
    if request.method == 'GET':
        return render_template('add_teacher.html')
    
    tid = request.form.get('tid')
    tname = request.form.get('tname')
    email = request.form.get('email')
    salary = request.form.get('salary')

    conn = pymysql.connect(host = db_config['host'],  user = db_config['user'], password = db_config['password'], database = db_config['database'])
    cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)

    sql = "INSERT INTO teachers (tid, tname, email, salary) VALUES (%s, %s, %s, %s)"
    cursor.execute(sql, (tid, tname, email, salary))
    conn.commit()

    cursor.close()
    conn.close()
    return '添加老师成功'

#在teachers表中查询数据
@app.route('/search/teachers', methods=['GET', 'POST'])
def get_teacher_tid():
    if request.method == 'GET':
        return render_template('input_teacher.html')
    
    tid = request.form.get('tid')
    if not tid:
        return render_template('show_single_teacher.html', teacher=None, error="老师编号不能为空")
    
    teacher = query_teacher_by_tid(tid)
    if not teacher:
        return render_template('show_single_teacher.html', teacher=None, error="未找到该老师")
    
    return render_template('show_single_teacher.html', teacher=teacher)

#teachers表的查询函数
def query_teacher_by_tid(tid):
    try:
        conn = pymysql.connect(host = db_config['host'],  user = db_config['user'], password = db_config['password'], database = db_config['database'])
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = "SELECT * FROM teachers WHERE tid = %s"
        cursor.execute(sql, (tid,))
        return cursor.fetchone()
    except Exception as e:
        print(f"数据库操作失败: {e}")
        return None
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


#courses表
#首先是获取courses表
@app.route('/show/courses')
def show_courses():
    try:
        # 获取请求的页码，默认为第 1 页
        page = request.args.get('page', 1, type=int)
        offset = (page - 1) * PER_PAGE

        # 连接数据库
        conn = pymysql.connect(host = db_config['host'],  user = db_config['user'], password = db_config['password'], database = db_config['database'])
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

        # 查询当前页的老师数据
        sql = "SELECT * FROM courses LIMIT %s OFFSET %s"
        cursor.execute(sql, (PER_PAGE, offset))
        courses = cursor.fetchall()

        # 查询学生总数
        cursor.execute("SELECT COUNT(*) AS total FROM courses")
        total_courses = cursor.fetchone()['total']

        # 计算总页数
        total_pages = (total_courses + PER_PAGE - 1) // PER_PAGE

        # 关闭连接
        cursor.close()
        conn.close()

        # 渲染模板
        return render_template('show_courses.html', courses=courses, page=page, total_pages=total_pages)

    except Exception as e:
        print(f"数据库操作失败: {e}")
        return render_template('show_courses.html', error="数据库操作失败，请稍后重试")

#在courses表中添加数据
@app.route('/add/course', methods=['GET', 'POST'])
def add_courses():
    if request.method == 'GET':
        return render_template('add_course.html')
    
    cid = request.form.get('cid')
    cname = request.form.get('cname')
    hour = request.form.get('hour')

    conn = pymysql.connect(host = db_config['host'],  user = db_config['user'], password = db_config['password'], database = db_config['database'])
    cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)

    sql = "INSERT INTO courses (cid, cname, houor) VALUES (%s, %s, %s)"
    cursor.execute(sql, (cid, cname, hour))
    conn.commit()

    cursor.close()
    conn.close()
    return '添加课程成功'

#在courses表中查询数据
@app.route('/search/courses', methods=['GET', 'POST'])
def get_course_cid():
    if request.method == 'GET':
        return render_template('input_course.html')
    
    cid = request.form.get('cid')
    if not cid:
        return render_template('show_single_course.html', course=None, error="课程编号不能为空")
    
    course = query_course_by_cid(cid)
    if not course:
        return render_template('show_single_course.html', course=None, error="未找到该课程")
    
    return render_template('show_single_course.html', course=course)

#courses表的查询函数
def query_course_by_cid(cid):
    try:
        conn = pymysql.connect(host = db_config['host'],  user = db_config['user'], password = db_config['password'], database = db_config['database'])
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = "SELECT * FROM courses WHERE cid = %s"
        cursor.execute(sql, (cid,))
        return cursor.fetchone()
    except Exception as e:
        print(f"数据库操作失败: {e}")
        return None
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

#choices表
#首先是获取choices表
@app.route('/show/choices')
def show_choices():
    try:
        # 获取请求的页码，默认为第 1 页
        page = request.args.get('page', 1, type=int)
        offset = (page - 1) * PER_PAGE

        # 连接数据库
        conn = pymysql.connect(host = db_config['host'],  user = db_config['user'], password = db_config['password'], database = db_config['database'])
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

        # 查询当前页的老师数据
        sql = "SELECT * FROM choices LIMIT %s OFFSET %s"
        cursor.execute(sql, (PER_PAGE, offset))
        choices = cursor.fetchall()

        # 查询学生总数
        cursor.execute("SELECT COUNT(*) AS total FROM choices")
        total_choices = cursor.fetchone()['total']

        # 计算总页数
        total_pages = (total_choices + PER_PAGE - 1) // PER_PAGE

        # 关闭连接
        cursor.close()
        conn.close()

        # 渲染模板
        return render_template('show_choices.html', choices=choices, page=page, total_pages=total_pages)

    except Exception as e:
        print(f"数据库操作失败: {e}")
        return render_template('show_choices.html', error="数据库操作失败，请稍后重试")

#在choices表中添加数据
@app.route('/add/choice', methods=['GET', 'POST'])
def add_choices():
    if request.method == 'GET':
        return render_template('add_choice.html')
    
    no = request.form.get('no')
    tid = request.form.get('tid')
    sid = request.form.get('sid')
    cid = request.form.get('cid')
    score = request.form.get('score')

    conn = pymysql.connect(host = db_config['host'],  user = db_config['user'], password = db_config['password'], database = db_config['database'])
    cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)

    sql = "INSERT INTO choices (no, sid, tid, cid, score) VALUES (%s, %s, %s, %s, %s)"  
    cursor.execute(sql, (no, sid, tid, cid, score))
    conn.commit()

    cursor.close()
    conn.close()
    return '添加选课记录成功'

#在choices表中查询数据
@app.route('/search/choices', methods=['GET', 'POST'])
def get_choice_no():
    if request.method == 'GET':
        return render_template('input_choice.html')
    
    no = request.form.get('no')
    if not no:
        return render_template('show_single_choice.html', choice=None, error="选课记录编号不能为空")
    
    choice = query_choice_by_no(no)
    if not choice:
        return render_template('show_single_choice.html', choice=None, error="未找到该选课记录")
    
    return render_template('show_single_choice.html', choice=choice)

#choices表的查询函数
def query_choice_by_no(no):
    try:
        conn = pymysql.connect(host = db_config['host'],  user = db_config['user'], password = db_config['password'], database = db_config['database'])
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = "SELECT * FROM choices WHERE no = %s"
        cursor.execute(sql, (no,))
        return cursor.fetchone()
    except Exception as e:
        print(f"数据库操作失败: {e}")
        return None
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

if __name__ == '__main__':
    app.run()